/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.ArrayList;
import model.config.MySQLQuery;
import model.pojo.*;
import model.util.MySQLProviders;
/**
 *
 * @author VuTienIT
 */
public class KhachHangDAO {
    
    public static int CapNhatSoDuTK(String TenDangNhap,double SoDuTaiKhoan,String dau, MySQLProviders helper)
    {
        int flag=-1;
      
        try
        {
            String sql = String.format("Update KhachHang Set SoDuTaiKhoan = SoDuTaiKhoan "+dau+" %e Where TenDangNhap='%s'",
                    SoDuTaiKhoan,TenDangNhap);
            flag = helper.executeUpdate(sql);
        }
        catch(Exception ex)
        {
            Logger.getLogger(KhachHangPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
    
        return flag;
    }
    
    public static int ThemKhachHang(KhachHangPOJO kh)
    {
        int flag=-1;
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        try
        {
            String sql = String.format("Insert into KhachHang (TenDangNhap,MatKhau,Email,SoDienThoai,NgayDangKy,MaKhuVuc,SoDuTaiKhoan,DiemTichLuy,SoBanBeDaMoi,Khoa) "
                    + "values ('%s','%s','%s','%s','"+kh.getNgayDangKy()+"',%d,1000,0,0,0)",
                    kh.getTenTruyCap(),kh.getMatKhau(),kh.getEmail(),kh.getSoDienThoai(),kh.getMaKhuVuc());
            flag = helper.executeUpdate(sql);
        }
        catch(Exception ex)
        {
            Logger.getLogger(KhachHangPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return flag;
    }
    
    public static int CapNhatDiaChiKhachHang(KhachHangPOJO kh)
    {
        int flag=-1;
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        try
        {
            String sql = String.format("Update KhachHang Set NguoiNhan='%s',MaKhuVuc='%d',MaQuan='%d',DiaChi='%s',"
                    + "SoDTNguoiNhan='%s' Where TenDangNhap='%s'",
                    kh.getNguoiNhan(),kh.getMaKhuVuc(),kh.getMaQuan(),kh.getDiaChi(),kh.getSoDTNguoiNhan(),kh.getTenTruyCap());
            flag = helper.executeUpdate(sql);
        }
        catch(Exception ex)
        {
            Logger.getLogger(KhachHangPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return flag;
    }
    
    public static int CapNhatTTKhachHang(KhachHangPOJO kh)
    {
        int flag=-1;
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        try
        {
            String sql = String.format("Update KhachHang Set MatKhau='%s',SoDienThoai='%s',MaKhuVuc='%d' Where TenDangNhap='%s'",
                    kh.getMatKhau(),kh.getSoDienThoai(),kh.getMaKhuVuc(),kh.getTenTruyCap());
            flag = helper.executeUpdate(sql);
        }
        catch(Exception ex)
        {
            Logger.getLogger(KhachHangPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return flag;
    }
    
    public static ArrayList<KhachHangPOJO> getDanhSach()
    {
        ArrayList<KhachHangPOJO> s = new ArrayList<KhachHangPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        try
        {
            String sql = "Select * From KhachHang";
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                KhachHangPOJO temp = new KhachHangPOJO();
                temp.setTenTruyCap(rs.getString("TenDangNhap"));
                temp.setMatKhau(rs.getString("MatKhau"));
                temp.setEmail(rs.getString("Email"));
                temp.setNguoiNhan(rs.getString("NguoiNhan"));
                temp.setSoDienThoai(rs.getString("SoDienThoai"));
                temp.setSoDTNguoiNhan(rs.getString("SoDTNguoiNhan"));
                temp.setMaKhuVuc(rs.getInt("MaKhuVuc"));
                temp.setMaQuan(rs.getInt("MaQuan"));
                temp.setDiaChi(rs.getString("DiaChi"));
                temp.setNgayDangKy(rs.getTimestamp("NgayDangKy"));
                temp.setDiemTichLuy(rs.getInt("DiemTichLuy"));
                temp.setSoBanBeDaMoi(rs.getInt("SoBanBeDaMoi"));
                temp.setSoDuTaiKhoan(rs.getDouble("SoDuTaiKhoan"));
                temp.setKhoa(rs.getBoolean("Khoa"));
                s.add(temp);
            }         
        }
        catch(SQLException ex)
        {
            Logger.getLogger(KhachHangPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    
    public static KhachHangPOJO DangNhap(String TenTruyCap,String MatKhau)
    {
        KhachHangPOJO temp = new KhachHangPOJO();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        try
        {
            String sql = String.format("Select * From KhachHang Where TenDangNhap='%s' and MatKhau='%s'",TenTruyCap,MatKhau);
             
            ResultSet rs = helper.executeQuery(sql);
            if(rs.next())
            {
                temp.setTenTruyCap(rs.getString("TenDangNhap"));
                temp.setMatKhau(rs.getString("MatKhau"));
                temp.setEmail(rs.getString("Email"));
                temp.setNguoiNhan(rs.getString("NguoiNhan"));
                temp.setSoDienThoai(rs.getString("SoDienThoai"));
                temp.setSoDTNguoiNhan(rs.getString("SoDTNguoiNhan"));
                temp.setMaKhuVuc(rs.getInt("MaKhuVuc"));
                temp.setMaQuan(rs.getInt("MaQuan"));
                temp.setDiaChi(rs.getString("DiaChi"));
                temp.setNgayDangKy(rs.getTimestamp("NgayDangKy"));
                temp.setDiemTichLuy(rs.getInt("DiemTichLuy"));
                temp.setSoBanBeDaMoi(rs.getInt("SoBanBeDaMoi"));
                temp.setSoDuTaiKhoan(rs.getDouble("SoDuTaiKhoan"));
                temp.setKhoa(rs.getBoolean("Khoa"));
            }         
        }
        catch(SQLException ex)
        {
            Logger.getLogger(KhachHangPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return temp;
    }
    
    public static KhachHangPOJO KiemTraTenDN(String TenTruyCap)
    {
        KhachHangPOJO temp = new KhachHangPOJO();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        try
        {
            String sql = String.format("Select * From KhachHang Where TenDangNhap='%s'",TenTruyCap);
             
            ResultSet rs = helper.executeQuery(sql);
            if(rs.next())
            {
                temp.setTenTruyCap(rs.getString("TenDangNhap"));
                temp.setMatKhau(rs.getString("MatKhau"));
                temp.setEmail(rs.getString("Email"));
                temp.setNguoiNhan(rs.getString("NguoiNhan"));
                temp.setSoDienThoai(rs.getString("SoDienThoai"));
                temp.setSoDTNguoiNhan(rs.getString("SoDTNguoiNhan"));
                temp.setMaKhuVuc(rs.getInt("MaKhuVuc"));
                temp.setMaQuan(rs.getInt("MaQuan"));
                temp.setDiaChi(rs.getString("DiaChi"));
                temp.setNgayDangKy(rs.getTimestamp("NgayDangKy"));
                temp.setDiemTichLuy(rs.getInt("DiemTichLuy"));
                temp.setSoBanBeDaMoi(rs.getInt("SoBanBeDaMoi"));
                temp.setSoDuTaiKhoan(rs.getDouble("SoDuTaiKhoan"));
                temp.setKhoa(rs.getBoolean("Khoa"));
            }         
        }
        catch(SQLException ex)
        {
            Logger.getLogger(KhachHangPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return temp;
    }
    
    public static int KiemTraTenDN_V2(String TenTruyCap)
    {
        int flag = -1;
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        try
        {
            String sql = String.format("Select * From KhachHang Where TenDangNhap='%s'",TenTruyCap);
             
            ResultSet rs = helper.executeQuery(sql);
            if(rs.next())
            {
                flag=1;
            }         
        }
        catch(SQLException ex)
        {
            Logger.getLogger(KhachHangPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return flag;
    }
    
    public static int CapNhatRefer(String tenDN)
    {    
        int flag=-1;
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        try
        {
            String sql = String.format("UPDATE KhachHang SET DiemTichLuy = DiemTichLuy + 100,SoBanBeDaMoi = SoBanBeDaMoi + 1 where TenDangNhap = '%s'",tenDN );
            flag = helper.executeUpdate(sql);
        }
        catch(Exception ex)
        {
            Logger.getLogger(KhachHangPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return flag;
    }
    
    public static KhachHangPOJO KiemTraEmail(String Email)
    {
        KhachHangPOJO temp = new KhachHangPOJO();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        try
        {
            String sql = String.format("Select * From KhachHang Where Email='%s'",Email);
             
            ResultSet rs = helper.executeQuery(sql);
            if(rs.next())
            {
                temp.setTenTruyCap(rs.getString("TenDangNhap"));
                temp.setMatKhau(rs.getString("MatKhau"));
                temp.setEmail(rs.getString("Email"));
                temp.setNguoiNhan(rs.getString("NguoiNhan"));
                temp.setSoDienThoai(rs.getString("SoDienThoai"));
                temp.setSoDTNguoiNhan(rs.getString("SoDTNguoiNhan"));
                temp.setMaKhuVuc(rs.getInt("MaKhuVuc"));
                temp.setMaQuan(rs.getInt("MaQuan"));
                temp.setDiaChi(rs.getString("DiaChi"));
                temp.setNgayDangKy(rs.getTimestamp("NgayDangKy"));
                temp.setDiemTichLuy(rs.getInt("DiemTichLuy"));
                temp.setSoBanBeDaMoi(rs.getInt("SoBanBeDaMoi"));
                temp.setSoDuTaiKhoan(rs.getDouble("SoDuTaiKhoan"));
                temp.setKhoa(rs.getBoolean("Khoa"));
            }         
        }
        catch(SQLException ex)
        {
            Logger.getLogger(KhachHangPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return temp;
    }

    
        public static ArrayList<KhachHangPOJO> getDanhSachCoPhanTrang(int trang) throws FileNotFoundException, IOException {
      ArrayList<KhachHangPOJO> ds = new ArrayList<KhachHangPOJO>(); 
      int soSanPhamTrenTrang = ThamSoDAO.laySoLuongTrenTrang();
      int batDau = (trang - 1) * soSanPhamTrenTrang;
      if(soSanPhamTrenTrang==batDau)
          batDau=1;     
      //mo ket noi
      MySQLProviders helper = new MySQLProviders();
      helper.open();
      
      //doc query tu file text
      //lay cau truy van tu file text
     MySQLQuery mySQLQuery=new MySQLQuery();
     mySQLQuery.Read("PHANTRANG");
     String sql=String.format(mySQLQuery.GetQuery(MySQLQuery.QueryKey.SELECT_ALL_KHACHHANG_PHANTRANG),batDau,soSanPhamTrenTrang);
      ResultSet rs = helper.executeQuery(sql);
      try {
         while (rs.next()) {
            KhachHangPOJO temp = new KhachHangPOJO();
            temp.setTenTruyCap(rs.getString("TenTruyCap"));
            temp.setMatKhau(rs.getString("MatKhau"));
            temp.setEmail(rs.getString("Email"));
            temp.setSoDienThoai(rs.getString("SoDienThoai"));
            temp.setMaKhuVuc(rs.getInt("MaKhuVuc"));
            temp.setDiemTichLuy(rs.getInt("DiemTichLuy"));
            temp.setSoBanBeDaMoi(rs.getInt("SoBanBeDaMoi"));
            temp.setSoDuTaiKhoan(rs.getDouble("SoDuTaiKhoan"));
            temp.setKhoa(rs.getBoolean("Khoa"));                
            ds.add(temp);
         }
      } catch (SQLException ex) {
         System.out.println(ex.getMessage());
      }
      helper.close();
      return ds;
   }
    
     public static KhachHangPOJO getById(String id){
       KhachHangPOJO obj=new KhachHangPOJO();
       MySQLProviders helper = new MySQLProviders();
       helper.open();
       ResultSet rs = helper.executeQuery("select *from khachhang where tendangnhap='"+id+"'");
       try {
           while (rs.next()) {            
            obj.setTenTruyCap(rs.getString("TenTruyCap"));
            obj.setMatKhau(rs.getString("MatKhau"));
            obj.setEmail(rs.getString("Email"));
            obj.setSoDienThoai(rs.getString("SoDienThoai"));
            obj.setMaKhuVuc(rs.getInt("MaKhuVuc"));
            obj.setDiemTichLuy(rs.getInt("DiemTichLuy"));
            obj.setSoBanBeDaMoi(rs.getInt("SoBanBeDaMoi"));
            obj.setSoDuTaiKhoan(rs.getDouble("SoDuTaiKhoan"));
            obj.setKhoa(rs.getBoolean("Khoa")); 
            
         }
       }catch(Exception e){
           
       }finally{
           helper.close();
       }
       return obj;
   }
  
}
